﻿using System;
using System.Collections.Generic;
using System.Text;
using X.DataFactory;
using System.Data;
using X.Entity;

namespace X.Data.Access
{
    public class ProductCategory : IProductCategory
    {
        public bool Create(Entity.ProductCategoryInfo categoryInfo)
        {
            string sSQL = @"INSERT INTO [X_ProductCategory]([CategoryName],[ParentCategoryId],[ShowOnMenu],[OrderNum],[IsShow],[Title],[Keyword],[Descript],[LongWord],[CategoryDescript])
                            VALUES(@CategoryName,@ParentCategoryId,@ShowOnMenu,@OrderNum,@IsShow,@Title,@Keyword,@Descript,@LongWord,@CategoryDescript)";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryName", DbType.String, categoryInfo.CategoryName);
            cmdParams.AddInParameter("@ParentCategoryId", DbType.Int32, categoryInfo.ParentCategoryId);
            cmdParams.AddInParameter("@ShowOnMenu", DbType.Boolean, categoryInfo.ShowOnMenu);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, categoryInfo.OrderNum);
            cmdParams.AddInParameter("@IsShow", DbType.Boolean, categoryInfo.IsShow);
            cmdParams.AddInParameter("@Title", DbType.String, categoryInfo.Title);
            cmdParams.AddInParameter("@Keyword", DbType.String, categoryInfo.Keyword);
            cmdParams.AddInParameter("@Descript", DbType.String, categoryInfo.Descript);
            cmdParams.AddInParameter("@LongWord", DbType.String, categoryInfo.LongWord);
            cmdParams.AddInParameter("@CategoryDescript", DbType.String, categoryInfo.CategoryDescript);
            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool Delete(int categoryId)
        {
            throw new NotImplementedException();
        }

        public Entity.ProductCategoryInfo GetCategory(int categoryId)
        {
            string sSQL = "SELECT * FROM X_ProductCategory WHERE CategoryId = @CategoryId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, categoryId);

            ProductCategoryInfo categoryInfo;

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                if (reader.Read())
                {
                    return CategoryFromReader(reader);
                }
                categoryInfo = new ProductCategoryInfo(true);
            }

            return categoryInfo;
        }

        public Entity.ProductCategoryInfo GetCategory(string categoryName)
        {
            string sSQL = "SELECT * FROM X_ProductCategory WHERE CategoryName = @CategoryName";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryName", DbType.String, categoryName);

            ProductCategoryInfo categoryInfo;

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                if (reader.Read())
                {
                    return CategoryFromReader(reader);
                }
                categoryInfo = new ProductCategoryInfo(true);
            }

            return categoryInfo;
        }

        public IList<Entity.ProductCategoryInfo> GetList()
        {
            string sSQL = @"SELECT * FROM X_ProductCategory ORDER BY ShowOnMenu,OrderNum";

            IList<ProductCategoryInfo> list = new List<ProductCategoryInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(CategoryFromReader(reader));
                }
            }

            return list;
        }

        public IList<ProductCategoryInfo> GetCategoryList()
        {
            string sSQL = @"SELECT * FROM X_ProductCategory WHERE ShowOnMenu = false ORDER BY ShowOnMenu,OrderNum";

            IList<ProductCategoryInfo> list = new List<ProductCategoryInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(CategoryFromReader(reader));
                }
            }

            return list;
        }

        public IList<ProductCategoryInfo> GetNavList()
        {
            string sSQL = @"SELECT * FROM X_ProductCategory WHERE ShowOnMenu = true ORDER BY ShowOnMenu,OrderNum";

            IList<ProductCategoryInfo> list = new List<ProductCategoryInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(CategoryFromReader(reader));
                }
            }

            return list;
        }

        public bool Update(Entity.ProductCategoryInfo categoryInfo)
        {
            string sSQL = @"UPDATE X_ProductCategory
                            SET CategoryName = @CategoryName
                            ,ParentCategoryId = @ParentCategoryId
                            ,ShowOnMenu = @ShowOnMenu
                            ,OrderNum = @OrderNum
                            ,IsShow = @IsShow
                            ,Title = @Title
                            ,Keyword = @Keyword
                            ,Descript = @Descript
                            ,LongWord = @LongWord
                            ,CategoryDescript = @CategoryDescript
                            WHERE CategoryId = @CategoryId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryName", DbType.String, categoryInfo.CategoryName);
            cmdParams.AddInParameter("@ParentCategoryId", DbType.Int32, categoryInfo.ParentCategoryId);
            cmdParams.AddInParameter("@ShowOnMenu", DbType.Boolean, categoryInfo.ShowOnMenu);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, categoryInfo.OrderNum);
            cmdParams.AddInParameter("@IsShow", DbType.Boolean, categoryInfo.IsShow);
            cmdParams.AddInParameter("@Title", DbType.String, categoryInfo.Title);
            cmdParams.AddInParameter("@Keyword", DbType.String, categoryInfo.Keyword);
            cmdParams.AddInParameter("@Descript", DbType.String, categoryInfo.Descript);
            cmdParams.AddInParameter("@LongWord", DbType.String, categoryInfo.LongWord);
            cmdParams.AddInParameter("@CategoryDescript", DbType.String, categoryInfo.CategoryDescript);
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, categoryInfo.CategoryId);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        private static ProductCategoryInfo CategoryFromReader(NullableDataReader reader)
        {
            ProductCategoryInfo categoryInfo = new ProductCategoryInfo();

            categoryInfo.CategoryId = reader.GetInt32("CategoryId");
            categoryInfo.CategoryName = reader.GetString("CategoryName");
            categoryInfo.ParentCategoryId = reader.GetInt32("ParentCategoryId");
            categoryInfo.ShowOnMenu = reader.GetBoolean("ShowOnMenu");
            categoryInfo.OrderNum = reader.GetInt32("OrderNum");
            categoryInfo.IsShow = reader.GetBoolean("IsShow");
            categoryInfo.Title = reader.GetString("Title");
            categoryInfo.Keyword = reader.GetString("Keyword");
            categoryInfo.Descript = reader.GetString("Descript");
            categoryInfo.LongWord = reader.GetString("LongWord");
            categoryInfo.CategoryDescript = reader.GetString("CategoryDescript");
            categoryInfo.ProductCount = reader.GetInt32("ProductCount");

            return categoryInfo;
        }

        public int GetProductCount(int categoryId)
        {
            string sSQL = @"SELECT COUNT(*) 
                            FROM X_Product 
                            WHERE CategoryId = @CategoryId OR SpecCategoryId = @CategoryId";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CategoryId", DbType.Int32, categoryId);

            return Convert.ToInt32(DBHelper.ExecuteScalarSql(sSQL, cmdParams));
        }
    }
}
